package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;

import ModelLayer.*;

public class DBFacility implements IFDBFacility {
	private Connection con;

	public DBFacility() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Facility getFacility(long ID) {
		String wClause = "  ID = '" + ID + "'";
		return singleWhere(wClause);
	}

	@Override
	public ArrayList<Facility> getFacilities() {
		return new ArrayList<Facility>(miscWhere(""));
	}

	public ArrayList<Facility> getFacilities(ActivityType at) {
		String wClause = " activityIDs like '%" + at.getID() + "%'";
		return new ArrayList<Facility>(miscWhere(wClause));
	}

	@Override
	public int insertFacility(Facility facility) {
		int rc = -1;

		facility.setID(GetMaxId.getMaxId("Select max(ID) from facility") + 1);

		String query = "INSERT INTO facility (ID, name, activityIDs) VALUES ('"
				+ facility.getID() + "','" + facility.getName() + "','"
				+ AIDs2String(facility) + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("Facility is not inserted");
		}
		return rc;
	}

	@Override
	public int updateFacility(long ID, Facility facility) {
		int rc = -1;

		String query = "UPDATE facility SET " + "name = '" + facility.getName()
				+ "'," + "activityIDs = '" + AIDs2String(facility) + "'"
				+ " WHERE ID = '" + ID + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in facility db: " + e);
		}
		return rc;
	}

	@Override
	public int removeFacility(long ID) {
		int rc = -1;
		String query = "DELETE FROM facility WHERE ID = '" + ID + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in facility db: " + ex);
		}
		return rc;
	}

	private LinkedList<Facility> miscWhere(String wClause) {
		ResultSet results;
		LinkedList<Facility> list = new LinkedList<Facility>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Facility f = new Facility();
				f = buildFacility(results);
				list.add(f);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Facility singleWhere(String wClause) {
		ResultSet results;
		Facility f = new Facility();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				f = buildFacility(results);
				stmt.close();
			} else {
				f = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return f;
	}

	private Facility buildFacility(ResultSet results) {
		Facility f = new Facility();
		try {
			f.setID(results.getLong("ID"));
			f.setName(results.getString("name"));
			String2AIDs(results.getString("activityIDs"), f);
		} catch (Exception e) {
			System.out.println("error in building the facility object: "
					+ e.getMessage());
		}
		return f;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT * FROM facility"; // ID, name, activityIDs

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	private String AIDs2String(Facility facility) {
		String aids = new String();
		for (ActivityType at : facility.getActivities()) {
			aids += at.getID() + ";";
		}
		return aids;
	}

	private void String2AIDs(String IDs, Facility f) {
		System.out.println("String2AIDs - Processing string: " + IDs);
		if (IDs.length() > 0 && IDs != "null" && IDs != null)
			for (String ID : IDs.split(";")) {
				System.out.println("String2AIDs - Activity #" + ID
						+ " has been added to Facility #" + f.getID() + ", "
						+ f.getName());
				f.addActivity(new ActivityType(Long.parseLong(ID)));
			}
	}

}